---
title: "sp72268_get_cancel_status stored procedure documentation"
summary: "Analysis of the sp72268_get_cancel_status Oracle function including business rules and operations."
status: draft
owner: "SAIF Corporation"
last_reviewed: 2026-08-31
source_schema: "WCIS_DBA"
source_name: "sp72268_get_cancel_status"
source_type: "PROCEDURE"
retrieval_status: "found"
retrieval_command: "legends oracle query --name sp72268_get_cancel_status"
retrieval_date: "2026-08-31"
---

# sp72268_get_cancel_status

## Overview

| Field | Value |
| --- | --- |
| **Schema** | `WCIS_DBA` |
| **Type** | Standalone function |
| **Database** | Oracle |
| **Status** | ✅ `found` — retrieved via Legends CLI |
| **Query executed** | `legends oracle query --name sp72268_get_cancel_status` |
| **Calling libraries** | `SAIF.Business.View.Policy` |
| **Calling classes** | `clsPolicyData` |

## Purpose

`sp72268_get_cancel_status` derives a display cancel status for a policy from its status group, demand
history, audit state, and open collection legal actions. It is called standalone by `clsPolicyData` and
also from inside `pk73094_policy_data.getpolicy` ([PR-BVP-018](pk73094-policy-data.md#business-rules)).

## Procedures / Methods

### sp72268_get_cancel_status

| Field | Value |
| --- | --- |
| **Returns** | `VARCHAR2` (function return value) |
| **Called by** | `clsPolicyData` cancel-status retrieval (`clsPolicyData.vb:2320`, used at `1728`); also called inside `pk73094_policy_data.getpolicy` |

**Description:** Dispatches on the policy status group to derive a cancel-status label, covering
pending-cancel, cancelled, and application-status branches (see rules PR-BVP-001 through PR-BVP-011 in
[Business Rules](#business-rules) below).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_policy_num` | IN | `NUMBER` | — | Policy number |
| *(return)* | RETURN | `VARCHAR2` | — | Cancel status label |

## Business Rules

Rules extracted from this function's logic:

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| PR-BVP-001 | Cancel-status derivation is dispatched on the policy status group | `POLICY_STATUS_CODE.policy_status_group_code` = `PENDCA` | Evaluate `pending_cancel_status` (demand check only) |
| PR-BVP-002 | Cancelled policies use the full cancel-status precedence chain | status group = `CANCEL` | Evaluate `cancel_status` in order: Judgment → Lien → Defaulted → Demanded → Pending Audit → Pending PR → Closed with Balance → default `Closed` |
| PR-BVP-003 | A policy is *Judgment* status when a judgment legal action is open | `COLLECTION_LEGAL_ACTION.COLLECTION_ACTION_CODE = '018'` AND `CANCEL_DATE IS NULL` | Return Judgment status |
| PR-BVP-004 | A policy is *Lien* status when an open lien legal action exists | `COLLECTION_LEGAL_ACTION.COLLECTION_ACTION_CODE IN ('016','017')` AND `CANCEL_DATE IS NULL` | Return Lien status |
| PR-BVP-005 | A policy is *Defaulted* when the demand response indicates default | `POLICY_DEMAND.COLLECTION_ACT_RESPONSE_CODE = 'D'` | Return Defaulted status |
| PR-BVP-006 | A policy is *Demanded* when a demand exists with no response recorded | `POLICY_DEMAND.COLLECTION_ACT_RESPONSE_CODE IS NULL` | Return Demanded status |
| PR-BVP-007 | A policy is *Pending Audit* when a physical audit is outstanding before cancellation | `POLICY_PERIOD_PAYROLL_AUDIT.AUDIT_STATUS_CODE IN ('A','R')` AND `audit_period_start_date < cancel_date` (latest `cancellation_seq_num`) AND `AUDIT_CODE.AUDIT_TYPE_CODE IN ('PHYS','PHYSR')` | Return Pending Audit status |
| PR-BVP-008 | A policy is *Pending PR* when an audit is outstanding but is not a physical audit | `AUDIT_STATUS_CODE IN ('A','I','R')` AND NOT EXISTS a matching physical audit | Return Pending PR status |
| PR-BVP-009 | A cancelled policy carrying a balance is reported as *Closed with Balance* | `NVL(pol_rec.current_balance, 0) <> 0` | Return Closed-with-Balance status; otherwise default `Closed` |
| PR-BVP-010 | Application-status policies not owned by WCIS are reported as `saifQuote` | status group = `APP` AND `pk73102_policy.does_wcis_own_submission(in_policy_num) = 'N'` AND derived cancel status IS NULL | Return `saifQuote` |
| PR-BVP-011 | Unhandled errors are surfaced as an application error, not silently swallowed | `EXCEPTION WHEN others` | `RAISE_APPLICATION_ERROR(-20002, 'Error in sp72268: ' \|\| SQLERRM)` |

> These rules are documented in [business-rules.md](../business-rules.md).

## Operations

Operations and processes implemented in this function:

| Operation | Description | Trigger |
| --- | --- | --- |
| Derive cancel status | Derives a display cancel status from policy status group and collection/audit state | `clsPolicyData` cancel-status retrieval (`clsPolicyData.vb:1728`, SQL at `2320`); also called inside `pk73094_policy_data.getpolicy` |

> These operations are also listed in [operations-processes.md](../operations-processes.md) with source reference back to this document.

## Source Code

<details>
<summary>PL/SQL function (specification and body)</summary>

```sql
FUNCTION sp72268_get_cancel_status (in_policy_num IN NUMBER)
   RETURN VARCHAR2
IS
BEGIN
   -- dispatch on policy status group
   IF v_status_group = 'PENDCA' THEN
      v_result := pending_cancel_status;      -- demand check only
   ELSIF v_status_group = 'CANCEL' THEN
      v_result := cancel_status;              -- Judgment -> Lien -> Defaulted ->
                                              -- Demanded -> Pending Audit ->
                                              -- Pending PR -> Closed w/Bal -> Closed
   ELSIF v_status_group = 'APP' THEN
      IF pk73102_policy.does_wcis_own_submission (in_policy_num) = 'N'
         AND v_cancel_status IS NULL
      THEN
         v_result := 'saifQuote';
      END IF;
   END IF;

   RETURN v_result;
EXCEPTION
   WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR (-20002, 'Error in sp72268: ' || SQLERRM);
END;
```

Call site (`clsPolicyData.vb:2320`):

```sql
select sp72268_get_cancel_status(:in_policy_num) from dual
```

</details>

## Related Documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)

## Sources
- Oracle DBA_SOURCE: `WCIS_DBA.sp72268_get_cancel_status`
- Legends CLI query executed 2026-08-31: `legends oracle query --name sp72268_get_cancel_status`
- Calling code: `SAIF.Business.View.Policy/clsPolicyData.vb:1728, 2320`